﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlServerCe;
using System.Data;
using System.Windows.Forms;

namespace QuanLySieuThi_08520114_08520220
{
    class controller_kho
    {
        private SqlCeDataAdapter temp_adapter;
        private DataTable temp_table;
        private SqlCeConnection con = null;

        public DataTable list_k()
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";

                    sql = "Select * From KHO";
                    temp_adapter = new SqlCeDataAdapter(sql, con);
                    temp_table = new DataTable();
                    temp_adapter.Fill(temp_table);
                    ToanCuc.disconnect(ref con);
                    return temp_table;

                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return null;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return null;
            }
            return null;
        }

        public DataTable list_mh_k(string k_id)
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";

                    sql = "Select k.*,m.MaMatHang From KHO_MATHANG k Left join MatHang m on (k.MatHangID = m.MatHangID) where KhoID = '" + k_id + "'";
                    temp_adapter = new SqlCeDataAdapter(sql, con);
                    temp_table = new DataTable();
                    temp_adapter.Fill(temp_table);
                    ToanCuc.disconnect(ref con);
                    return temp_table;
                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return null;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return null;
            }
            return null;
        }

        public string get_thongtin_mh(string ma_mh)
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";

                    sql = "Select SoLuongTonToiThieu From KHO_MATHANG Where MatHangID in (select MatHangID From MATHANG where MaMatHang = '" +ma_mh+ "')";
                    temp_adapter = new SqlCeDataAdapter(sql, con);
                    temp_table = new DataTable();
                    temp_adapter.Fill(temp_table);
                    ToanCuc.disconnect(ref con);

                    if (temp_table.Rows.Count == 0)
                        return "";

                    return temp_table.Rows[0]["SoLuongTonToiThieu"].ToString();

                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return "";
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return "";
            }
            return "";
        }

        public bool update_ton(string kho_id,string ma_mh, string ton)
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";
                    sql = "Update KHO_MATHANG Set SoLuongTonToiThieu = '" + ton + "' Where MatHangID in (select MatHangID From MATHANG where MaMatHang = '" + ma_mh + "') and KhoID='"+kho_id+"'";
                    SqlCeCommand cmd = new SqlCeCommand(sql, con);
                    cmd.ExecuteNonQuery();
                    ToanCuc.disconnect(ref con);
                    return true;
                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return false;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return false;
            }

            return false;
        }

        public bool del_mathang(string mh_id)
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";
                    sql = "Delete From KHO_MATHANG Where MatHangID = '" + mh_id +"'";
                    SqlCeCommand cmd = new SqlCeCommand(sql, con);
                    cmd.ExecuteNonQuery();
                    ToanCuc.disconnect(ref con);
                    return true;
                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return false;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return false;
            }

            return false;
        }

        public bool update_kho(string[] data)
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";
                    sql = "Select Count(*) From KHO Where ( MaKho ='" + data[1] + "' or TenKho ='" + data[2] + "') And KhoID != '" + data[0] + "'";
                    SqlCeCommand cmd = new SqlCeCommand(sql, con);
                    int count = (int)cmd.ExecuteScalar();

                    if (count > 0)
                    {
                        MessageBox.Show("Mã Kho hoặc Tên Kho đã có trong database !", "Thông báo");
                        return false;
                    }

                    cmd.CommandText = "Update KHO Set MaKho = '" + data[1] + "', TenKho = '"+data[2]+"', DiaChiKho = '"+data[3]+"', DienTichKho = '"+data[4]+"' Where KhoID = '" + data[0] + "'";
                    cmd.ExecuteNonQuery();
                    ToanCuc.disconnect(ref con);
                    return true;
                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return false;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return false;
            }

            return false;
        }

        public bool add_kho(string[] data)
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";
                    sql = "Select Count(*) From KHO Where MaKho ='" + data[1] + "' or TenKho ='" + data[2] + "'";
                    SqlCeCommand cmd = new SqlCeCommand(sql, con);
                    int count = (int)cmd.ExecuteScalar();

                    if (count > 0)
                    {
                        MessageBox.Show("Mã Kho hoặc Tên Kho đã có trong database !", "Thông báo");
                        return false;
                    }

                    cmd.CommandText = "Insert into KHO (MaKho,TenKho,DiaChiKho,DienTichKho) Values ('" + data[1] + "','" + data[2] + "','" + data[3] + "','" + data[4] + "')";
                    cmd.ExecuteNonQuery();
                    ToanCuc.disconnect(ref con);
                    return true;
                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return false;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return false;
            }

            return false;
        }

        public bool del_kho(string k_id)
        {
            try
            {
                bool check = ToanCuc.OpenDB(ref con);
                if (check == true)
                {
                    string sql = "";
                    sql = "Delete From KHO Where KhoID = '" + k_id + "'";
                    SqlCeCommand cmd = new SqlCeCommand(sql, con);
                    cmd.ExecuteNonQuery();
                    ToanCuc.disconnect(ref con);
                    return true;
                }
            }
            catch (SqlCeException ex)
            {
                ToanCuc.DisplayError(ex);
                return false;
            }
            catch (SystemException exx)
            {
                MessageBox.Show(exx.Message);
                return false;
            }

            return false;
        }

    }
}
